﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.ReportSource;
using System.Data.SqlClient;
using System.Configuration;
using Xyz.Web.Admin.DataSet;

namespace Xyz.Web.Admin.Report
{
    public partial class FlightSeatTakenReport : System.Web.UI.Page
    {
        ReportDocument rDoc = null;
        FlightDS ds = null;
        string conString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
        SqlConnection connection = null;


        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                txt_FromDate.Text = DateTime.Now.ToString("dd MMM yyyy");
                txt_ToDate.Text = DateTime.Now.ToString("dd MMM yyyy");
            }
            else
            {
                BindReport();
            }   
        }

        protected void btn_Preview_Click(object sender, EventArgs e)
        {
            BindReport();   
        }

        protected void cv_Date_ServerValidate(object source, ServerValidateEventArgs args)
        {
            if (DateTime.Parse(txt_ToDate.Text).CompareTo(DateTime.Parse(txt_FromDate.Text)) < 0)
                args.IsValid = false;
            else
                args.IsValid = true;
        }

        private void BindReport()
        {
            string sFromDate = txt_FromDate.Text;
            string sToDate = txt_ToDate.Text;

            rDoc = new ReportDocument();
            ds = new FlightDS();
            DataTable db = new DataTable("FlightSeatTaken");

            connection = new SqlConnection(conString);
            string sqlQuery;
            sqlQuery = " select Flights.FlightCode,Flights.DepartureDateTime,Flights.ArrivalDateTime, " +
                       " (select AirportName from Airports where AirportCode = Flights.DepartureAirport) as DepartureAirport, " +
                       " (select AirportName from Airports where AirportCode = Flights.ArrivalAirport) as ArrivalAirport, " +
                       " Aircrafts.TotalSeat as TotalSeatNo, " +
                       " (select COUNT(*)  from FlightSeats where FlightSeats.FlightId = Flights.FlightId) as TakenSeatNo, " +
                       " (convert(VARCHAR,convert(decimal(5,2),(1.00 * (select COUNT(*)  from FlightSeats where FlightSeats.FlightId = Flights.FlightId))/Aircrafts.TotalSeat  * 100)) + '%') as Percentage " + 
                       " from Flights join Aircrafts on Flights.AircraftId = Aircrafts.AircraftId ";

            if (rbl_Date.SelectedIndex == 0)
                sqlQuery += ("where DepartureDateTime >= '" + DateTime.Parse(sFromDate) + "' and DepartureDateTime <= '" + DateTime.Parse(sToDate).AddDays(1) + "' ");
            else
                sqlQuery += ("where ArrivalDateTime >= '" + DateTime.Parse(sFromDate) + "' and ArrivalDateTime <= '" + DateTime.Parse(sToDate).AddDays(1) + "' ");

            sqlQuery += "Order by FlightCode ";
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            connection.Open();
            adapter.Fill(db);
            ds.Tables[0].Merge(db);

            if (ds.Tables[0].Rows.Count > 0)
            {
                rDoc.Load(Server.MapPath("FlightSeatTakenRpt.rpt"));
                rDoc.Refresh();
                rDoc.SetDataSource(ds);
                rDoc.SetParameterValue("SearchBy", rbl_Date.SelectedItem.Text);
                rDoc.SetParameterValue("FromDate", txt_FromDate.Text);
                rDoc.SetParameterValue("ToDate", txt_ToDate.Text);

                crv_FlightSeatTaken.ReportSource = rDoc;
                crv_FlightSeatTaken.DataBind();
                crv_FlightSeatTaken.Visible = true;
                lblNoRecord.Visible = false;
            }
            else
            {
                lblNoRecord.Visible = true;
                crv_FlightSeatTaken.Visible = false;
            }
        }

        protected void rbl_Date_SelectedIndexChanged(object sender, EventArgs e)
        {
            lblNoRecord.Visible = false;
            crv_FlightSeatTaken.Visible = false;
        }
    }
}